<?php
defined('SYSPATH') or die('No direct access allowed.');
//计算销量
class Service_daysale{

	//计算单个sku的销量
	static function count_daysale($sku,$date_start,$date_end)
	{
		//$type = array(-4,1,3,5,6,9,14,15,16,17,24,43);//国内销售出库
        /*
         * $type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50);
         * @time20140818
         * 新增的有三个，53是WISH   54是敦煌    55是新蛋
         */
        /*$type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50,53,54,55);*/

        /**
         * @time 20141004
         * @author zhjx
         * 52燕邮宝
         * 53WISH
         * 54敦煌
         * 55新蛋
         * 添加燕邮宝 52
         */
        /*$type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50,52,53,54,55);*/

        /**
         * @time 20141006
         * @author zhjx
         *
         */
        $type = array(-4,1,3,5,6,8,9,16,17,24,43,45,50,51,52,53,54,55,60,70,451,510);

        /**
         * orders_status状态,客服撤单,亏本撤单，库存撤单，是6,7,16,17这些
         */
        $status = array(6,7,16,17);

        $count_sku = DB::select ( array ('erp_orders_products.orders_sku','sku'),
				array (DB::expr ( 'SUM(erp_orders_products.item_count)' ),'counts') )
						->from ( 'erp_orders_products' )
						->join('erp_orders')
						->on('erp_orders_products.erp_orders_id', '=','erp_orders.erp_orders_id')
						->where ( 'erp_orders.orders_export_time', '>=', $date_start )
						->where ( 'erp_orders.orders_export_time', '<', $date_end )
						->where ('erp_orders.orders_type', 'in', $type)
                        ->where (DB::expr('ifnull(erp_orders.orders_status,0)'), 'not in', $status)
						->where('erp_orders_products.orders_sku','=',$sku)
						->group_by ( 'erp_orders_products.orders_sku' );

        $count_sku = $count_sku ->execute ( "readonlyerp" )
						->current();
		return $count_sku?$count_sku['counts']:0;
	}

	//计算单个sku的销量
	static function count_daysale2($date_start,$date_end)
	{
		//国内销售出库
		//$type = array(-4,1,3,5,6,9,14,15,16,17,24,43);
		/*
		 * $type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50);
		 * @time20140818
		 * 新增的有三个，53是WISH   54是敦煌    55是新蛋
		 */
        /*$type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50,53,54,55);*/
//        $type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50,52,53,54,55);

        //225 北京邮政挂号　61燕文订单
        //229 sunyou
        //238 荷兰邮政 20150618新增
        //239 泉州邮局 20150622 新增
        //240 WISH邮订单类型 20150713新增
        //241 郑州邮政 20150721新增
        //249 20150728添加
        //250 马邮 20150728添加
        //259 新加坡DGM 20150824添加
        //新增订单类型 JZ：253  苏州平邮：254 //20150902添加订单类型
        //20150211新增　226  哈尔滨E快递,227  顺丰速运,228 国际e邮宝,666  线下EUB
        //230其他出库渠道20150904添加
        //255 印尼订单 256 东莞平邮 20150911加
        //京华达邮政  257   顺邮宝 258 20151101添加
        /**
         * @time 20141006
         * @author zhjx
         */
        $type = array(-4,1,3,5,6,8,9,16,17,24,43,45,50,51,52,53,54,55,60,61,70,225,226,227,247,228,229,230,238,239,240,241,249,250,251,252,253,254,255,256,257,258,259,451,510,666);
        /**
         * orders_status状态,客服撤单,亏本撤单，库存撤单，是6,7,16,17这些 101是问题订单
         */
        $status = array(6,7,16,17,101);
		//循环次数 多少个周期（一个周期7天）
		$index=(strtotime($date_end)-strtotime($date_start))/(7*24*3600);
		
		$skus_counts=array();
		
		for($i=0;$i<$index;$i++){
			
			/**
			 * 往前倒推一个周期作为开始
			 * -28 -21 -14 -7 0
			 * 开始 周期0 0 -7
			 * 接着 周期1 -7 -14
			 * 接着 周期2 -14 -21
			 * 接着 周期3  -21 -28
			 */
			 
			$date_start=date('Y-m-d',strtotime($date_end.'-7 days'));
			
			/**
			 * 
			 *array(
			 * sku =>
			 * count =>
			 * )
			 */
			$skus_count = DB::select ( array (DB::expr('upper(trim(erp_orders_products.orders_sku))'),'sku'),
					   array (DB::expr ( 'SUM(erp_orders_products.item_count)' ),'count') )
								->from ( 'erp_orders_products' )
								->join('erp_orders')
								->on('erp_orders_products.erp_orders_id', '=','erp_orders.erp_orders_id')
								->where ( 'erp_orders.orders_export_time', '>=', $date_start )
								->where ( 'erp_orders.orders_export_time', '<', $date_end )
								->where ('erp_orders.orders_type', 'in', $type)
                                ->where (DB::expr('ifnull(erp_orders.orders_status,0)'), 'not in', $status)
								->where(DB::expr('upper(trim(erp_orders_products.orders_sku))'),'!=','')
								->group_by (DB::expr('upper(trim(erp_orders_products.orders_sku))') )
								->execute ( "readonlyerp" )
 								->as_array();
			
			$date_end = $date_start;
			
			
			
			/**
			 * 每一周期一个数组
			 * $skus_count[0]
			 * $skus_count[1]
			 * $skus_count[2]
			 * $skus_count[3]
			 */
			$skus_counts[$i]=$skus_count;
		}
			
	//	var_dump($count_sku);exit;
		return $skus_counts;
	}


	//计算全部sku的销量 $days = 7/14/21/28天
	static function count_all_daysale($date_start,$date_end)
	{
		$echo = "";
		//国内销售出库
		//$type = array(-4,1,3,5,6,9,14,15,16,17,24,43);
        /*
         * $type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50);
         * @time20140818
         * 新增的有三个，53是WISH   54是敦煌    55是新蛋
         */
        /*$type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50,53,54,55);*/
        /*$type = array(-4,1,3,5,6,9,14,15,16,17,24,43,45,50,52,53,54,55);*/

        /**
         * @time 20141006
         * @author zhjx
         */
        //$type = array(-4,1,3,5,6,8,9,16,17,24,40,43,45,50,51,52,53,55,60,70,451,510);
		
		/**
		 * 全部的sku
		 */
//      ---------------------------------------kdw------------------------------------
//		$skus = DB::select(array(DB::expr('upper(erp_products_data.products_sku)'),'sku'))
//			->from('erp_products_data')
//			->execute('readonlyerp')
//			->as_array();

        $skus=Skusystemapi::getAllSku();
//		---------------------------------------kdw-end--------------------------------
		
		
			$all_skus = array();//全部的SKU
			if(count($skus) >0)
			{
				/**
				 * 全部的SKU
				 */
				foreach ($skus as $sku) 
				{
					if(isset($sku['sku']) and trim($sku['sku'])){
						$all_skus[$sku['sku']]["sku"] = trim($sku['sku']);
					}
				}
			}

			$echo .="#all_sku".count($all_skus);
			$echo .="#time".date("Y-m-d H:i:s");
			
			if(empty($all_skus))
			{
				return NULL;
			}
			
			//获取到所有的sku的销量 0:28-21 1:21-14 2:14-7 3:7-0
			$skus_counts=Service_daysale::count_daysale2($date_start,$date_end);
			
			if(empty($skus_counts))
			{
				return NULL;
			}
			
			/**
			 * 销量信息
			 *
			 * 0 => -07    ~  0 	{sku => 销量}
			 * 1 => -14  ~  -7 	{sku => 销量}
			 * 2 => -14 ～ -21	{sku => 销量}
			 * 3 => -21 ～ -28 	{sku => 销量}
			 * 
			 */
			foreach ($skus_counts as $index=>$skus_count){
				
				/**
				 * 获取每一组销量
				 */
				foreach ($skus_count as $sku_count){
					
					/**
					 * 有销量的数组 sku => 销量
					 * 
					 */
					 
					 if(array_key_exists(trim($sku_count['sku']), $all_skus))
					 {
					 	$all_skus[trim($sku_count['sku'])][$index] = $sku_count['count'];
					 }
				}
			}
			
			/**
			 * 补全没有销量的sku，销量为0
			 */
			foreach ($all_skus as $sku => $sku_arr) {
				
				for($j =0;$j<count($skus_counts);$j++)
				{
					if(!isset($sku_arr[$j]))
					{
						$all_skus[$sku][$j] = 0;
					}
				}
			}
			
			$echo .= "#sale_sku".date("Y-m-d H:i:s");
			//echo $echo;
			///print_r($all_skus);
			//exit;
			
			/**
			 * 将销量保存到数据库
			 */
			Service_daysale::batch_save_warning($all_skus);

			$echo .="#save_all".date("Y-m-d H:i:s");
			
			/**
			 * 计算日均销量
			 */
			Service_daysale::count_daysale_weighting();
			
			$echo .="#daysale_count".date("Y-m-d H:i:s");
			
			$echo .="\n";
			
			echo $echo;
		
	}
	
	//统计代码的执行时间
	static 	function getmicrotime(){
		list($usec, $sec) = explode(" ",microtime());
		return ((float)$usec + (float)$sec);
	}
	
	
	/**
	 * 批量保存预警信息
	 * @param array $skus_arr
     * @return int
	 */
	static function batch_save_warning(array $skus_arr)
	{
	
	
		if(empty($skus_arr))
		{
			return NULL;
		}
		
		/**
		 * 拼接insert/update语句
		 */
		$sql = "insert into product_attached ";
		$sql .= " (sku,daysales7,daysales14,daysales21,daysales28,createtime,updatatime,wave_days,target_time) values ";
		
		$index=7;
		
		/**
		 * sku => 四个销量
		 */
		 $insert = "";
		foreach($skus_arr as $sku=>$sale_arr){
				
			
			$daysales7 = 0;
			$daysales14 = 0;
			$daysales21 = 0;
			$daysales28 = 0;
			
			if($sale_arr and is_array($sale_arr))
			{
				$daysales7 = (isset($sale_arr[0])?$sale_arr[0]:0);
				$daysales14 = $daysales7 + (isset($sale_arr[1])?$sale_arr[1]:0);
				$daysales21 = $daysales14 + (isset($sale_arr[2])?$sale_arr[2]:0);
				$daysales28 = $daysales21 + (isset($sale_arr[3])?$sale_arr[3]:0);
			}
	
			/**
			 * 拼接insert部分语句
			 */
			$insert .=$insert ? (", ('".$sku."',".$daysales7.",".$daysales14.",".$daysales21.",".$daysales28.",'".date('Y-m-d H:i:s')."','00-00-00 00:00:00',3,7)"):("('".$sku."',".$daysales7.",".$daysales14.",".$daysales21.",".$daysales28.",'".date('Y-m-d H:i:s')."','00-00-00 00:00:00',3,7)");
				
		}
			
		
		/**
		 * 拼接update部分语句
		 */
		$update = "";
		$update .= " on duplicate key update ";
		$update .= "daysales7=values(daysales7),daysales14=values(daysales14),daysales21=values(daysales21),daysales28=values(daysales28),updatatime='".date('Y-m-d H:i:s')."'";
	
		//echo $sql.$insert.$update;exit;
		$row_total = DB::query(Database::INSERT,$sql.$insert.$update)->execute('readonlyerp');
		
		return $row_total;
	
	}

	//计算加权日均销量
	static function count_daysale_weighting()
	{
		$sql = 'update product_attached set daysales=((daysales7)/7 * 0.5 + (daysales14)/14 * 0.3 + (daysales28)/28 * 0.2)';
		
		$row_total = DB::query(Database::UPDATE,$sql)->execute('readonlyerp');
		return $row_total;
	}

}